#!/usr/bin/env python
# -*- coding:utf-8 -*-
import openpyxl


class Excel:
    def __init__(self, filename):
        """
        初始化函数
        :param filename:
        """
        self.File = filename
        self.readWorkBook = None
        self.saveWorkBook = None
        self.updateWorkBook = None

    def Read(self, data=[]):
        """
        读取Excel文件数据函数
        :param sheetname: str类型，表名
        :param data: list类型，二维，[[表名, 地址], [表名, 地址], ……]
        :return: list类型，二维，[[单元格, 数据], [单元格, 数据], ……]
        """
        self.readWorkBook = openpyxl.load_workbook(self.File)
        re = []
        for sheetdata in data:
            ws = self.readWorkBook[sheetdata[0]]
            re.append([sheetdata[0], sheetdata[1], ws[sheetdata[1]].value])
        return re

    def Create(self, data):
        """
        创建Excel文件并录入数据函数
        :param data: list类型，四维，
        data=[
            [
                "表名",
                [
                    ["单元格地址", "数据"],
                    ["单元格地址", "数据"],
                    ……
                ]
            ],
            [
                "表名",
                [
                    ["单元格地址", "数据"],
                    ["单元格地址", "数据"],
                    ……
                ]
            ],
            ……
        ]
        :return:
        """
        self.saveWorkBook = openpyxl.Workbook()
        if len(data) == 1:
            ws = self.saveWorkBook.active
            ws.title = data[0]
            for cell in data[1]:
                ws[cell[0]] = cell[1]
            self.saveWorkBook.save(self.File)
        else:
            self.saveWorkBook.save(self.File)
            for sheet in data:
                self.Update(sheet)
            self.Delete("Sheet")

    def Update(self, data):
        """
        更新Excel文件数据函数
        :param data: list类型，三维
        data = [
            "表名",
             [["单元格地址", "数据"],
              ["单元格地址", "数据"]，
              ……
              ]
        ]
        :return:
        """
        self.updateWorkBook = openpyxl.load_workbook(self.File)
        if data[0] in self.updateWorkBook.sheetnames:
            ws = self.updateWorkBook[data[0]]
            for cell in data[1]:
                ws[cell[0]] = cell[1]
        else:
            ws = self.updateWorkBook.create_sheet(data[0])
            for cell in data[1]:
                ws[cell[0]] = cell[1]
        self.updateWorkBook.save(self.File)

    def Delete(self, sheet):
        """
        删除Excel文件表函数
        :param sheet: str类型，表明
        :return:
        """
        wb = openpyxl.load_workbook(self.File)
        wb.remove(wb[sheet])
        wb.save(self.File)

    def MaxRow(self, sheet):
        """
        取指定表最大有效行数
        :param sheet: str类型，表名
        :return:int类型，最大行数
        """
        wb = openpyxl.load_workbook(self.File)
        ws = wb[sheet]
        return ws.max_row

    def MaxCol(self, sheet):
        """
        取指定表最大有效列数
        :param sheet: str类型，表名
        :return: int类型，最大列数
        """
        wb = openpyxl.load_workbook(self.File)
        ws = wb[sheet]
        return ws.max_column

    def ColIntToChr(self, num):
        """
        数字列号转换为字母列号
        :param num: int类型，数字列号
        :return: str类型，字母列号
        """
        re = ""
        while num > 0:
            num, Remainder = divmod(num - 1, 26)
            re = chr(65 + Remainder) + re
        return re
